﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Linq;
using System.Reflection;
using System.Text;
using MySql.Data.MySqlClient;

namespace CWCS.Common
{
    public static  class DBHelper
    {
        /// <summary>
        /// 返回DbConnection 返回数据库连接
        /// </summary>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public static DbConnection DbConnectionFromConnectionString(string connectionString)
        {
            DbConnection connection = null;
            OleDbConnectionStringBuilder builder = new System.Data.OleDb.OleDbConnectionStringBuilder(connectionString);
            string provider = builder["Provider"].ToString();
            if (provider.StartsWith("MSDASQL"))
            {
                string odbcConnectionString = "";
                if (builder.ContainsKey("Extended Properties")) odbcConnectionString = builder["Extended Properties"].ToString();
                else if (builder.ContainsKey("Data Source") && !string.IsNullOrEmpty(builder["Data Source"].ToString())) odbcConnectionString = "DSN=" + builder["Data Source"].ToString();
                if (odbcConnectionString != "" && builder.ContainsKey("Initial Catalog")) odbcConnectionString += ";DATABASE=" + builder["Initial Catalog"].ToString();
                if (odbcConnectionString != "" && builder.ContainsKey("User ID")) odbcConnectionString += ";UID=" + builder["User ID"].ToString();
                if (odbcConnectionString != "" && builder.ContainsKey("Password")) odbcConnectionString += ";PWD=" + builder["Password"].ToString();

                connection = new OdbcConnection(odbcConnectionString);
            }
            else if (provider.StartsWith("OraOLEDB"))
            {
                connection = new Oracle.DataAccess.Client.OracleConnection(connectionString);
            }
            else if (provider.StartsWith("MSDAORA"))
            {

                connection = new System.Data.OracleClient.OracleConnection(connectionString); 
            }
            else
            {
                connection = new OleDbConnection(connectionString);
            }
            return connection;
        }


        ///<summary>
        /// 生成插入数据的sql语句。
        /// </summary>
        /// <param name="database"></param>
        /// <param name="command"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        private static IEnumerable<string> GenerateInserSql(DataTable table)
        {
            foreach (DataRow dataRow in table.Rows)
            {
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.AppendFormat("INSERT INTO {0} VALUES(", table.TableName);
                AssertFlag assertFlag = new AssertFlag();
                foreach (DataColumn dataColumn in table.Columns)
                {
                    if (!assertFlag.AssertTrue())
                    {
                        stringBuilder.Append(",");
                    }
                    if (dataRow[dataColumn] == DBNull.Value)
                    {
                        stringBuilder.Append("NULL");
                    }
                    else if (dataColumn.DataType == typeof(string) || dataColumn.DataType == typeof(DateTime))
                    {
                        stringBuilder.AppendFormat("'{0}'", dataRow[dataColumn]);
                    }
                    else if (dataColumn.DataType == typeof(DateTime?))
                    {
                        stringBuilder.AppendFormat("'{0}'", dataRow[dataColumn] == null ? DateTime.Now.ToString("yyyy/MM/dd hh24:mi:ss") : dataRow[dataColumn]);
                    }
                    else
                    {
                        stringBuilder.Append(dataRow[dataColumn]);
                    }
                }
                stringBuilder.Append(")");
                yield return stringBuilder.ToString();
            }
            yield break;
        }


        private static Tuple<string, object[]> GenerateInserSql<T>(T entity, bool isParamer = false)
        {
            if (entity == null)
                throw new ArgumentException("The database entity can not be null.");

            Type entityType = entity.GetType();
            var table = entityType.GetProperties().Where(i => i.Name != "IsValid"
                 && i.GetValue(entity, null) != null
                 && !i.PropertyType.IsEnum
                 && i.PropertyType != typeof(DateTime?)
                 && (i.PropertyType.IsValueType || i.PropertyType == typeof(string))).ToArray();

            var arguments = new List<object>();
            var fieldbuilder = new StringBuilder();
            var valuebuilder = new StringBuilder();

            fieldbuilder.Append(" INSERT INTO " + string.Format("{0}", entityType.Name.Trim()) + " (");

            foreach (var member in table)
            {
                object value = member.GetValue(entity, null);
                if (value != null)
                {
                    if (arguments.Count != 0)
                    {
                        fieldbuilder.Append(", ");
                        valuebuilder.Append(", ");
                    }

                    fieldbuilder.Append(member.Name);
                    if (member.PropertyType == typeof(string)
                        || member.PropertyType == typeof(DateTime)
                        || member.PropertyType == typeof(DateTime?)
                        || member.PropertyType == typeof(Boolean?)
                        || member.PropertyType == typeof(Boolean)
                        )
                        if (!isParamer)
                        {
                            valuebuilder.Append("'{" + arguments.Count + "}'");
                        }
                        else
                        {
                            valuebuilder.Append(":" + member.Name);
                        }
                    else
                        if (!isParamer)
                        {
                            valuebuilder.Append("{" + arguments.Count + "}");
                        }
                        else
                        {
                            valuebuilder.Append(":" + member.Name);
                        }
                    if (value is string)
                        value = value.ToString().Replace("'", "char(39)");
                    arguments.Add(value);
                }
            }


            fieldbuilder.Append(") Values (");

            fieldbuilder.Append(valuebuilder.ToString());

            fieldbuilder.Append(")");

            return new Tuple<string, object[]>(fieldbuilder.ToString(), arguments.ToArray());
        }

        /// <summary>
        /// 构建Delete语句串
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="entity"></param>
        /// <returns></returns>
        private static Tuple<string, object[]> CreateDeleteSQL<T>(T entity)
        {
            if (entity == null)
                throw new ArgumentException("The database entity can not be null.");

            Type entityType = entity.GetType();
            List<string> pkList = new List<string>();
            if (pkList == null || pkList.Count == 0)
                throw new ArgumentException("The Table entity have not a primary key.");

            List<object> arguments = new List<object>();
            StringBuilder builder = new StringBuilder();
            builder.Append(" Delete from " + string.Format("[{0}]", entityType.Name));

            builder.Append(" WHERE ");
            bool firstPrimaryKey = true;

            foreach (var primaryField in pkList)
            {
                if (firstPrimaryKey)
                    firstPrimaryKey = false;
                else
                    builder.Append(" AND ");

                Type pkType = entityType.GetProperty(primaryField).GetType();
                object val = entityType.GetProperty(primaryField).GetValue(entity, null);
                // builder.Append(GetEqualStatment(primaryField, arguments.Count, pkType));
                arguments.Add(val);
            }
            return new Tuple<string, object[]>(builder.ToString(), arguments.ToArray());
        }

        public static string DoSql<T>(IEnumerable<T> list, SQLType sqlType, params string[] fieldParams)
        {
            var sqlstr = new StringBuilder();
            switch (sqlType)
            {
                case SQLType.Insert:
                    int c_index = 0;
                    list.ToList().ForEach(i =>
                    {
                        if (c_index == 0)
                        {
                            Tuple<string, object[]> sql = GenerateInserSql(i, true);
                            sqlstr.AppendFormat(sql.Item1, sql.Item2);
                        }
                        c_index++;
                    });
                    break;
                case SQLType.Update:
                    list.ToList().ForEach(i =>
                    {
                        //暂时未实现
                       // Tuple<string, object[]> sql = CreateUpdateSql(i, fieldParams);
                       // sqlstr.AppendFormat(sql.Item1, sql.Item2);
                    });
                    break;
                case SQLType.Delete:
                    list.ToList().ForEach(i =>
                    {
                        //暂时未实现

                        //Tuple<string, object[]> sql = CreateDeleteSql(i);
                        // sqlstr.AppendFormat(sql.Item1, sql.Item2);
                    });
                    break;
                default:
                    throw new ArgumentException("请输入正确的参数");
            }
            return sqlstr.ToString();
        }


        public static string GenerateUpdateSql<T>(T entity, string tableName, string conditionColumnName,string ProviderName,out List<DbParameter> parameters)
        {
            parameters = new List<DbParameter>();
            StringBuilder res = new StringBuilder();
            res.AppendFormat("update {0} set ", tableName);
            var props = entity.GetType().GetProperties();
            string wherePart = null;
            foreach (PropertyInfo item in props)
            {
                var propVal = item.GetValue(entity, null);
                var hasValue = propVal != null ;
                if (hasValue)
                {
                    var filedname = item.Name;
                    if (ProviderName=="System.Data.OracleClient")
                    {
                        parameters.Add(new System.Data.OracleClient.OracleParameter { Value = propVal, ParameterName = filedname });  
                    }
                    else if (ProviderName=="Oracle.DataAccess.Client")
                    {
                        parameters.Add(new Oracle.DataAccess.Client.OracleParameter { Value = propVal, ParameterName = filedname });
                    }
                    else if (ProviderName == "MySql.Data.MySqlClient")
                    {
                        parameters.Add(new MySqlParameter { Value = propVal, ParameterName = filedname });
                    }
                    if (filedname.ToLower().Equals(conditionColumnName.ToLower()))
                    {
                        wherePart = string.Format(" where {0}=:{0} ", filedname);
                    }
                    else
                    {
                        res.AppendFormat(" {0}=:{0} ,", filedname);
                    }
                }
            }
            res.Remove(res.Length - 1, 1);
            res.Append(wherePart);
            return res.ToString();
        }

        public static Type GetListItemType<T>(this IList<T> list)
        {
            return typeof(T);
        }
    }
}
